package l2ft.gameserver.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javolution.text.TextBuilder;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

/**
 * @author Intrepid
 * 
 *         basic BoneCP connector, needs testing and fine tuning
 * 
 */
public class DatabaseFactory
{
	private static final Logger _log = LoggerFactory.getLogger(DatabaseFactory.class);
	
	private static BoneCP _connectionPool = null;
	private static BoneCPConfig config = new BoneCPConfig();
	
	private DatabaseFactory()
	{
		
	}
	
	public static void initConnection(String DBUrl, String DBUserName, String DBPassword)
	{
		try
		{
			try
			{
				// load the database driver
				Class.forName("com.mysql.jdbc.Driver");
			}
			catch (Exception e)
			{
				e.printStackTrace();
				return;
			}
			
			/**
			 * Sets the JDBC connection URL.
			 */
			config.setJdbcUrl(DBUrl);
			
			/**
			 * Sets username to use for connections.
			 */
			config.setUsername(DBUserName);
			
			/**
			 * Sets password to use for connections.
			 */
			config.setPassword(DBPassword);
			
			/**
			 * Sets the minimum number of connections that will be contained in every partition.
			 */
			config.setMinConnectionsPerPartition(5);
			
			/**
			 * Sets the maximum number of connections that will be contained in every partition.
			 * Setting this to 5 with 3 partitions means you will have 15 unique connections to the
			 * database. Note that the connection pool will not create all these connections in one
			 * go but rather start off with minConnectionsPerPartition and gradually increase
			 * connections as required.
			 */
			config.setMaxConnectionsPerPartition(10);
			
			/**
			 * Sets the acquireIncrement property. When the available connections are about to run
			 * out, BoneCP will dynamically create new ones in batches. This property controls how
			 * many new connections to create in one go (up to a maximum of
			 * maxConnectionsPerPartition). Note: This is a per partition setting.
			 */
			config.setAcquireIncrement(2);
			
			/**
			 * Sets number of partitions to use. In order to reduce lock contention and thus improve
			 * performance, each incoming connection request picks off a connection from a pool that
			 * has thread-affinity, i.e. pool[threadId % partition_count]. The higher this number,
			 * the better your performance will be for the case when you have plenty of short-lived
			 * threads. Beyond a certain threshold, maintenance of these pools will start to have a
			 * negative effect on performance (and only for the case when connections on a partition
			 * start running out). Default: 2, minimum: 1, recommended: 2-4
			 */
			config.setPartitionCount(2);
			
			/**
			 * Sets the idleConnectionTestPeriod. This sets the time (in minutes), for a connection
			 * to remain idle before sending a test query to the DB. This is useful to prevent a DB
			 * from timing out connections on its end. Do not use aggressive values here! Default:
			 * 240 min, set to 0 to disable
			 */
			config.setIdleConnectionTestPeriodInMinutes(240);
			
			/**
			 * Sets the idleConnectionTestPeriod. This sets the time (in seconds), for a connection
			 * to remain idle before sending a test query to the DB. This is useful to prevent a DB
			 * from timing out connections on its end. Do not use aggressive values here! Default:
			 * 240 min, set to 0 to disable
			 */
			config.setIdleConnectionTestPeriodInSeconds(14400);
			
			/**
			 * Sets Idle max age (in min). The time (in minutes), for a connection to remain unused
			 * before it is closed off. Do not use aggressive values here! Default: 60 minutes, set
			 * to 0 to disable.
			 */
			config.setIdleMaxAgeInMinutes(60);
			
			/**
			 * Sets Idle max age (in seconds). The time (in seconds), for a connection to remain
			 * unused before it is closed off. Do not use aggressive values here! Default: 60
			 * minutes, set to 0 to disable.
			 */
			config.setIdleMaxAgeInSeconds(3600);
			
			/**
			 * Sets statementsCacheSize setting. The number of statements to cache.
			 */
			config.setStatementsCacheSize(0);
			
			/**
			 * Sets number of helper threads to create that will handle releasing a connection. When
			 * this value is set to zero, the application thread is blocked until the pool is able
			 * to perform all the necessary cleanup to recycle the connection and make it available
			 * for another thread. When a non-zero value is set, the pool will create threads that
			 * will take care of recycling a connection when it is closed (the application dumps the
			 * connection into a temporary queue to be processed asychronously to the application
			 * via the release helper threads). Useful when your application is doing lots of work
			 * on each connection (i.e. perform an SQL query, do lots of non-DB stuff and perform
			 * another query), otherwise will probably slow things down.
			 */
			config.setReleaseHelperThreads(3);
			
			/**
			 * Instruct the pool to create a helper thread to watch over connection acquires that
			 * are never released (or released twice). This is for debugging purposes only and will
			 * create a new thread for each call to getConnection(). Enabling this option will have
			 * a big negative impact on pool performance.
			 */
			config.setCloseConnectionWatch(false);
			
			/**
			 * If enabled, log SQL statements being executed.
			 */
			config.setLogStatementsEnabled(false);
			
			/**
			 * Sets the number of ms to wait before attempting to obtain a connection again after a
			 * failure.
			 */
			config.setAcquireRetryDelayInMs(7000);
			
			/**
			 * Set to true to force the connection pool to obtain the initial connections lazily.
			 */
			config.setLazyInit(false);
			
			/**
			 * Set to true to enable recording of all transaction activity and replay the
			 * transaction automatically in case of a connection failure.
			 */
			config.setTransactionRecoveryEnabled(false);
			
			/**
			 * After attempting to acquire a connection and failing, try to connect these many times
			 * before giving up. Default 5.
			 */
			config.setAcquireRetryAttempts(5);
			
			/**
			 * Queries taking longer than this limit to execute are logged.
			 */
			config.setQueryExecuteTimeLimitInMs(0);
			
			/**
			 * Sets the Pool Watch thread threshold. The pool watch thread attempts to maintain a
			 * number of connections always available (between minConnections and maxConnections).
			 * This value sets the percentage value to maintain. For example, setting it to 20 means
			 * that if the following condition holds: Free Connections / MaxConnections <
			 * poolAvailabilityThreshold new connections will be created. In other words, it tries
			 * to keep at least 20% of the pool full of connections. Setting the value to zero will
			 * make the pool create new connections when it needs them but it also means your
			 * application may have to wait for new connections to be obtained at times. Default:
			 * 20.
			 */
			config.setPoolAvailabilityThreshold(20);
			
			/**
			 * If set to true, the pool will not monitor connections for proper closure. Enable this
			 * option if you only ever obtain your connections via a mechanism that is guaranteed to
			 * release the connection back to the pool (eg Spring's jdbcTemplate, some kind of
			 * transaction manager, etc).
			 */
			config.setDisableConnectionTracking(false);
			
			/**
			 * Sets the maximum time (in milliseconds) to wait before a call to getConnection is
			 * timed out. Setting this to zero is similar to setting it to Long.MAX_VALUE Default: 0
			 * ( = wait forever )
			 */
			config.setConnectionTimeoutInMs(0);
			
			/**
			 * Sets the no of ms to wait when close connection watch threads are enabled. 0 = wait
			 * forever.
			 */
			config.setCloseConnectionWatchTimeoutInMs(0);
			
			/**
			 * Sets number of statement helper threads to create that will handle releasing a
			 * statement. When this value is set to zero, the application thread is blocked until
			 * the pool and JDBC driver are able to close off the statement. When a non-zero value
			 * is set, the pool will create threads that will take care of closing off the statement
			 * asychronously to the application via the release helper threads). Useful when your
			 * application is opening up lots of statements otherwise will probably slow things
			 * down.
			 */
			config.setStatementReleaseHelperThreads(0);
			
			/**
			 * Sets the maxConnectionAge in seconds. Any connections older than this setting will be
			 * closed off whether it is idle or not. Connections currently in use will not be
			 * affected until they are returned to the pool.
			 */
			config.setMaxConnectionAgeInSeconds(0);
			
			_connectionPool = new BoneCP(config);
		}
		catch (SQLException e)
		{
			_log.error("cannot connect to the database!");
		}
	}
	
	public static String safetyString(String... whatToCheck)
	{
		// NOTE: Use brace as a safty percaution just incase name is a reserved word
		String braceLeft = "`";
		String braceRight = "`";
		
		String result = "";
		for (String word : whatToCheck)
		{
			if (result != "")
			{
				result += ", ";
			}
			result += braceLeft + word + braceRight;
		}
		return result;
	}
	
	public static void shutdown()
	{
		_connectionPool.shutdown();
	}
	
	private static DatabaseFactory _instance;
	
	public static DatabaseFactory getInstance()
	{
		if(_instance == null)
			_instance = new DatabaseFactory();
		return _instance;
	}
	
	public static Connection getConnection() // throws SQLException
	{
		Connection con = null;
		
		while (con == null)
		{
			try
			{
				con = _connectionPool.getConnection();
			}
			catch (SQLException e)
			{
				_log.warn("L2DatabaseFactory: getConnection() failed, trying again " + e);
			}
		}
		return con;
	}
	
	/**
	 * Should be deleted once try-with statements are done
	 * 
	 * @param con
	 */
	public static void close(Connection con)
	{
		if (con == null)
			return;
		
		try
		{
			con.close();
		}
		catch (SQLException e)
		{
			_log.warn("L2DatabaseFactory: Failed to close database connection!", e);
		}
	}
	
	public static void optimize() throws SQLException
	{
		Connection con = null;
		try
		{
			con = getConnection();
			
			final Statement st = con.createStatement();
			
			final ArrayList<String> tables = new ArrayList<>();
			{
				final ResultSet rs = st.executeQuery("SHOW FULL TABLES");
				
				while (rs.next())
				{
					final String tableType = rs.getString(2/*"Table_type"*/);
					
					if (tableType.equals("VIEW"))
						continue;
					
					tables.add(rs.getString(1));
				}
				
				rs.close();
			}
			
			{
				final ResultSet rs = st.executeQuery("CHECK TABLE " + StringUtils.join(tables, ","));
				
				while (rs.next())
				{
					final String table = rs.getString("Table");
					final String msgType = rs.getString("Msg_type");
					final String msgText = rs.getString("Msg_text");
					
					if (msgType.equals("status"))
						if (msgText.equals("OK"))
							continue;
					
					_log.warn("TableOptimizer: CHECK TABLE " + table + ": " + msgType + " -> " + msgText);
				}
				
				rs.close();
				
				_log.info("TableOptimizer: Database tables have been checked.");
			}
			
			{
				final ResultSet rs = st.executeQuery("ANALYZE TABLE " + StringUtils.join(tables, ","));
				
				while (rs.next())
				{
					final String table = rs.getString("Table");
					final String msgType = rs.getString("Msg_type");
					final String msgText = rs.getString("Msg_text");
					
					if (msgType.equals("status"))
						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
							continue;
					
					_log.warn("TableOptimizer: ANALYZE TABLE " + table + ": " + msgType + " -> " + msgText);
				}
				
				rs.close();
				
				_log.info("TableOptimizer: Database tables have been analyzed.");
			}
			
			{
				final ResultSet rs = st.executeQuery("OPTIMIZE TABLE " + StringUtils.join(tables, ","));
				
				while (rs.next())
				{
					final String table = rs.getString("Table");
					final String msgType = rs.getString("Msg_type");
					final String msgText = rs.getString("Msg_text");
					
					if (msgType.equals("status"))
						if (msgText.equals("OK") || msgText.equals("Table is already up to date"))
							continue;
					
					if (msgType.equals("note"))
						if (msgText.equals("Table does not support optimize, doing recreate + analyze instead"))
							continue;
					
					_log.warn("TableOptimizer: OPTIMIZE TABLE " + table + ": " + msgType + " -> " + msgText);
				}
				
				rs.close();
				
				_log.info("TableOptimizer: Database tables have been optimized.");
			}
			
			st.close();
		}
		finally
		{
			close(con);
		}
	}
	
	public static void backup()
	{
		final String databaseName = config.getJdbcUrl().replaceAll("^.*/", "");
		
		_log.info("DatabaseBackupManager: backing up `" + databaseName + "`...");
		
		final Process run;
		try
		{
			final ArrayList<String> commands = new ArrayList<>();
			commands.add("mysqldump");
			commands.add(" --user=" + config.getUsername()); // The MySQL user name to use when connecting to the server
			commands.add(" --password=" + config.getPassword()); // The password to use when connecting to the server
			commands.add("--compact"); // Produce more compact output.
			commands.add("--complete-insert"); // Use complete INSERT statements that include column names
			commands.add("--default-character-set=utf8"); // Use charset_name as the default character set
			commands.add("--extended-insert"); // Use multiple-row INSERT syntax that include several VALUES lists
			commands.add("--lock-tables"); // Lock all tables before dumping them
			commands.add("--quick"); // Retrieve rows for a table from the server a row at a time
			commands.add("--skip-triggers"); // Do not dump triggers
			commands.add(databaseName); // db_name
			
			final ProcessBuilder pb = new ProcessBuilder(commands);
			pb.directory(new File("."));
			
			run = pb.start();
		}
		catch (Exception e)
		{
			_log.warn("DatabaseBackupManager: Could not execute mysqldump!", e);
			return;
		}
		
		writeBackup(databaseName, run);
	}
	
	protected static final void writeBackup(String databaseName, Process run)
	{
		try
		{
			boolean success = false;
			
			InputStream in = null;
			try
			{
				in = run.getInputStream();
				
				success = writeBackup(databaseName, in);
			}
			catch (IOException e)
			{
				_log.warn("DatabaseBackupManager: Could not make backup:", e);
			}
			finally
			{
				IOUtils.closeQuietly(in);
			}
			
			if (!success)
			{
				BufferedReader br = null;
				try
				{
					br = new BufferedReader(new InputStreamReader(run.getErrorStream()));
					
					for (String line; (line = br.readLine()) != null;)
						_log.warn("DatabaseBackupManager: " + line);
				}
				catch (Exception e)
				{
					_log.warn("DatabaseBackupManager: Could not make backup:", e);
				}
				finally
				{
					IOUtils.closeQuietly(br);
				}
			}
			
			run.waitFor();
		}
		catch (Exception e)
		{
			_log.warn("DatabaseBackupManager: Could not make backup:", e);
		}
	}
	
	protected static final boolean writeBackup(String databaseName, InputStream in) throws IOException
	{
		FileUtils.forceMkdir(new File("backup/database"));
		
		final Date time = new Date();
		
		final TextBuilder tb = new TextBuilder();
		tb.append("backup/database/DatabaseBackup_");
		tb.append(new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(new Date()));
		tb.append("_uptime");
		tb.append(".zip");
		
		final File backupFile = new File(tb.toString());
		
		int written = 0;
		ZipOutputStream out = null;
		try
		{
			out = new ZipOutputStream(new FileOutputStream(backupFile));
			out.setMethod(ZipOutputStream.DEFLATED);
			out.setLevel(Deflater.BEST_COMPRESSION);
			out.setComment("L2jFree Schema Backup Utility\r\n\r\nBackup date: "
					+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS z").format(new Date()));
			out.putNextEntry(new ZipEntry(databaseName + ".sql"));
			
			byte[] buf = new byte[4096];
			for (int read; (read = in.read(buf)) != -1;)
			{
				out.write(buf, 0, read);
				
				written += read;
			}
		}
		finally
		{
			IOUtils.closeQuietly(in);
			IOUtils.closeQuietly(out);
		}
		
		if (written == 0)
		{
			backupFile.delete();
			return false;
		}
		
		_log.info("DatabaseBackupManager: Database `" + databaseName + "` backed up successfully in "
				+ (System.currentTimeMillis() - time.getTime()) / 1000 + " s.");
		return true;
	}
}